-- @owner: opentestcase009
-- @date: 2023/1/4
-- @testpoint: connect by子句,存在循环行时不加nocycle，合理报错


--step1:创建普通表，插入数据,存在循环数据;expect:建表成功，插入数据成功
drop table if exists t_connectby_015 cascade;
create table t_connectby_015(name varchar, id int, fatherid int);
insert into t_connectby_015 values('A', 1, 0), ('B', 2, 1),('C',3,1),('D',4,1),('E',5,2),('F',0,1),('G',1,2);
select * from t_connectby_015;

--step2:不加nocycle关键字，进行递归查询;expect:合理报错
select * from t_connectby_015 start with fatherid=1 connect by prior fatherid = id;
select * from t_connectby_015 start with id=1 connect by prior id = fatherid;

--step3:结合层次递归查询函数查看递归层级;expect:合理报错
select sys_connect_by_path(name,'-') as path, *, level from t_connectby_015 connect by prior fatherid = id limit 10;
select sys_connect_by_path(name,'-') as path, *, level from t_connectby_015 connect by prior id = fatherid limit 10;

--step4:清理环境;expect:成功
drop table t_connectby_015 cascade;
